using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TrainTicket.VO;

namespace TrainTicket.DAO
{
    public class TicketDAO
    {
        private DbConnection _conn;

        public TicketDAO()
        {
            _conn = new DbConnection();
        }

        public DataTable GetTicketById(int ticketNo)
        {
            SqlParameter param = new SqlParameter("@TicketNo", SqlDbType.Int);
            param.Value = ticketNo;

            return _conn.ExecuteDataTable("sp_GetTicketById", param);
        }

        public DataTable Reports(DateTime dateStart, DateTime dateEnd, string groupBy, string status)
        {
            SqlParameter[] param = new SqlParameter[4];

            param[0] = new SqlParameter("@DateStart", SqlDbType.DateTime);
            param[0].Value = dateStart;

            param[1] = new SqlParameter("@DateEnd", SqlDbType.DateTime);
            param[1].Value = dateEnd;

            param[2] = new SqlParameter("@GroupBy", SqlDbType.NVarChar, 50);
            param[2].Value = groupBy;

            param[3] = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            param[3].Value = status;

            return _conn.ExecuteDataTable("sp_Reports", param);
        }

        public DataTable GetTotalFeesByYear(string year)
        {
            SqlParameter param = new SqlParameter("@Year", SqlDbType.NVarChar, 50);
            param.Value = year;

            return _conn.ExecuteDataTable("sp_GetTotalOrderByYear", param);
        }

        public DataTable GetTotalOrderByCustomer(string year)
        {
            SqlParameter param = new SqlParameter("@Year", SqlDbType.NVarChar, 50);
            param.Value = year;

            return _conn.ExecuteDataTable("sp_GetTotalOrderByCustomer", param);
        }


        public DataTable GetAllTicket()
        {
            return _conn.ExecuteDataTable("sp_GetAllTicket", null);
        }

        public DataTable GetAllTicketPending()
        {
            return _conn.ExecuteDataTable("sp_GetAllTicketPending", null);
        }

        public DataTable GetAllTicketBySearch(string customer, DateTime date, string status)
        {
            SqlParameter[] param = new SqlParameter[3];

            param[0] = new SqlParameter("@CustomerName", SqlDbType.NVarChar, 50);
            param[0].Value = customer;

            param[1] = new SqlParameter("@Date", SqlDbType.DateTime);
            param[1].Value = date;

            param[2] = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            param[2].Value = date;
            return _conn.ExecuteDataTable("sp_GetAllTicketBySearch", null);
        }

        public int InsertTicket(Ticket ticket)
        {
            SqlParameter[] param = new SqlParameter[8];

            param[0] = new SqlParameter("@Date", SqlDbType.DateTime);
            param[0].Value = ticket.Date;

            param[1] = new SqlParameter("@ListTrainID", SqlDbType.Int);
            param[1].Value = ticket.ListTrainID;

            param[2] = new SqlParameter("@CustomerID", SqlDbType.Int);
            param[2].Value = ticket.CustomerID;

            param[3] = new SqlParameter("@AccountID", SqlDbType.Int);
            param[3].Value = ticket.AccountID;

            param[4] = new SqlParameter("@NumberSeat", SqlDbType.NVarChar, 50);
            param[4].Value = ticket.NumberSeat;

            param[5] = new SqlParameter("@PromoteID", SqlDbType.Int);
            param[5].Value = ticket.PromoteID;

            param[6] = new SqlParameter("@TotalFees", SqlDbType.Float);
            param[6].Value = ticket.TotalFees;

            param[7] = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            param[7].Value = ticket.Status;

            return _conn.ExecuteNonQuery("sp_InsertTicket", param);
        }

        public int InsertTicketCustomer(Ticket ticket)
        {
            SqlParameter[] param = new SqlParameter[7];

            param[0] = new SqlParameter("@Date", SqlDbType.DateTime);
            param[0].Value = ticket.Date;

            param[1] = new SqlParameter("@ListTrainID", SqlDbType.Int);
            param[1].Value = ticket.ListTrainID;

            param[2] = new SqlParameter("@CustomerID", SqlDbType.Int);
            param[2].Value = ticket.CustomerID;

            param[3] = new SqlParameter("@NumberSeat", SqlDbType.NVarChar, 50);
            param[3].Value = ticket.NumberSeat;

            param[4] = new SqlParameter("@PromoteID", SqlDbType.Int);
            param[4].Value = ticket.PromoteID;

            param[5] = new SqlParameter("@TotalFees", SqlDbType.Float);
            param[5].Value = ticket.TotalFees;

            param[6] = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            param[6].Value = ticket.Status;

            return _conn.ExecuteNonQuery("sp_InsertTicketCustomer", param);
        }

        public int UpdateStatusTicket(int ticketNo, string status, int accountID)
        {
            SqlParameter[] param = new SqlParameter[3];

            param[0] = new SqlParameter("@TicketNo", SqlDbType.Int);
            param[0].Value = ticketNo;

            param[1] = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            param[1].Value = status;

            param[2] = new SqlParameter("@AccountID", SqlDbType.Int);
            param[2].Value = accountID;

            return _conn.ExecuteNonQuery("sp_UpdateStatusTicket", param);
        }

        public int UpdateStatusTicketReturn(int ticketNo, string status, float fareReturnCustomer, int listTrainID, string numberSeat, int accountID)
        {
            SqlParameter[] param = new SqlParameter[6];

            param[0] = new SqlParameter("@TicketNo", SqlDbType.Int);
            param[0].Value = ticketNo;

            param[1] = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            param[1].Value = status;

            param[2] = new SqlParameter("@FareReturnCustomer", SqlDbType.Float);
            param[2].Value = fareReturnCustomer;

            param[3] = new SqlParameter("@ListTrainID", SqlDbType.Int);
            param[3].Value = listTrainID;

            param[4] = new SqlParameter("@NumberSeat", SqlDbType.NVarChar, 50);
            param[4].Value = numberSeat;

            param[5] = new SqlParameter("@AccountID", SqlDbType.Int);
            param[5].Value = accountID;

            return _conn.ExecuteNonQuery("sp_UpdateStatusTicketReturn", param);
        }

        public int GetTotalOrder()
        {
            return _conn.ExecuteScalar("sp_GetTotalOrder", null);
        }

        public float GetTotalSales()
        {
            return _conn.ExecuteScalar("sp_GetTotalSales", null);
        }

        public int DeleteTicket(int id)
        {
            SqlParameter param  = new SqlParameter("@TicketNo", SqlDbType.Int);
            param.Value = id;

            return _conn.ExecuteNonQuery("sp_DeleteTicket", param);
        }
    }
}
